(Sun Microsystems, Inc.)(1997).iso/products/Cobb/ordlogo.gif)
By Jeff Warner
You've probably prepared sales reports for your company. Typical sales reports are printed on greenbar paper with some sort of impact printer. If you're really trying to do a good job, you might print the report from a laser printer. You then send the report to the marketing people, who look through it and use a spreadsheet to dig in and analyze the data.
Wouldn't it be nice if they could use a program that worked straight from the database? Imagine if the application could provide charts for comparing the performance of two products. Imagine not having to kill a tree each time you run a report.
Developer/2000's Graphics Designer provides developers with an interactive graphics tool that is integrated into the database. Let's use the graphics tool to create a graphical marketing package for the Summit Sporting Goods Company.
Developer/2000 includes a sample set of tables for the Summit Sporting Goods Company. Normally the script to create the tables is located in the \orawin\forms45\sql\ summit2.sql file. If you or the DBA have not created these tables, create them now using the @ command inside SQL*Plus.
If the sample tables are set up, go ahead and start the Graphics Designer and log into the database. However, before we start creating the charts, we should look at the requirements for our marketing package.
The request for this report came straight from the Vice President of Sales. He is responsible for worldwide sales, and he wants better tools to use to analyze the data. He specified the following requirements:
Just to make it challenging (and realistic!), he needs the program by Friday so he can use it in a presentation.
Well, this looks like it's going to be fun. We'll break this problem into three parts: First, we'll create the queries; then we'll lay out the reports; and finally, we'll add code to make all of those features work together. Let's get started with the queries.
This report will use two queries--one to extract sales information by region and the other to provide sales information by company in a specific sales region. Rather than work out the SQL, we'll provide the code for your use.
Expand the Disp1 node and select the Query node in the Object Navigator. Select Add from the toolbar. In the SQL Statement box, type in the following:
select
r.name, count(*) "sales"
from
s_customer c, s_ord o,
s_region r
where
c.id = o.customer_id and
c.region_id = r.id
group by
r.name;
This query selects the entries in the Sales field for each region. Name this query Sales by Region and click OK. Next we need to create a query to handle the details of the regions. Repeat the procedure above to open the SQL Statement box and type the following:
select
c.name, count(*) "Sales"
from
s_customer c, s_ord o
where
c.id = o.customer_id and
c.region_id = (select id from s_region r
where
r.name = 'Asia')
group by
c.name;
Our second query returns sales grouped by company within each region. Name this query Sales by Company and click OK. As we explain in the section on linking the layers on the next page, we'll replace 'Asia' with a parameter that holds the region information that the user supplies so that our charts return the appropriate information. Let's move on to the next step.
Each report in Graphics Designer is set up in the Layout Editor. We'll use this tool to create two charts as well as some additional controls.
Activate the Layout Editor and select the Chart tool. Click and drag the mouse to draw a 3"x3" box on the screen. When you release the mouse button, the Chart Genie window will prompt you for a query to use for the chart. Select Sales by Region and click OK. Now you must fill out the Chart Properties window. Name the chart cRegion and title it Sales by Region. Select the Column with shadows chart type, as shown in Figure A. Click OK to save the chart.
Next, let's change some of the labels. Double-click on the Name label, and in the Axis Properties window, enter Region Name for the Custom Label field and click OK. Select the Sales by Region label and under Menu Format, select Font. Change the font to MS Sans Serif, set the point size to 18, and click OK. Let's take a look at our chart.
Click on the Run icon on the toolbar or select Run from the File menu. After you save the chart to the file system, the Sales by Region query will run and display the results on the screen as shown in Figure B. Exit Graphics runtime, and we'll move on to create the next chart.
You can use layers to group information that you want to display together. Our next chart will be on the second layer of this display layout. We'll swap layers when we drill down on a region.
To create a new layer, select the Layout node in Object Navigator and select Layers from the Tools menu. Click on the New button and then select the layer1 name and replace it with lCompany. Click Rename to rename it. Go ahead and rename layer0 to lRegion by selecting layer0 from the list, typing in lRegion, and clicking the Rename button. Now we need to activate the new layer and hide the lRegion layer. Select lCompany and click the Activate button. Now select the lRegion layer and click the Hide/Show button so that lRegion has a minus sign in front of it. Also click the Top button to move lRegion to the top. Click OK and move to the Layout Editor window.
Make another 3"x3" chart and this time use the Sales by Company query. Name the chart cCompany and title it Sales by Company. Select the Pie chart with shadow and click OK. Resize the chart until it fits in the window and then change the title's font to match the font and style from the other layer (MS Sans Serif 18 point). Save this chart and test it with the Run command. Now let's take a look at linking the two charts together.
Now that we've built both charts, we'll link them so that when the user clicks on part of the Sales by Region chart, the Sales by Company chart will appear with the correct region. There are several steps involved in making this work. We have to set up a parameter, change a few object properties, and then write two short PL/SQL procedures. We'll start with the parameter.
We use the parameter to pass data between the two charts. The parameter will hold the name of the region the user selected, and the Sales by Company chart will read the value and display the appropriate data. For additional information about parameters, please refer to the sidebar, "A Note About Parameters," below. To create the parameter, select the Parameter node and select Create from the Navigate menu. Name the parameter region_name, make sure the type is Char, and set the initial value to 'Asia' (include the quotes). Click OK to create the parameter.
Now we can use the parameter in our query. Edit the query sales by company and replace 'Asia' with :region_name, then click OK. Your query will now read the value from region_name when it runs. Now we need to modify the Sales by Region chart to set the value of the region_name parameter.
Bring up the Layer Settings window again, but this time activate lRegion and hide lCompany. This will make the Sales by Region layer visible in the Layout Editor. Activate the Layout Editor window so we can add the drill-down code.
Click on one of the bars of the chart to select it and then double-click on it. The Layout Editor will bring up the Object Properties window. Select the New button next to the Procedure text box, and Layout Editor will bring up the PL/SQL editor. Type in the code shown in Figure C. Then click the Compile button and make sure the code compiles without errors.
PROCEDURE Activate_lCompany (
buttonobj IN og_object,
hitobj IN og_object,
win IN og_window,
eventinfo IN og_event) IS
the_layer OG_LAYER;
BEGIN
the_layer := OG_GET_LAYER('lCompany');
OG_ACTIVATE_LAYER(the_layer, win);
the_layer := OG_GET_LAYER('lRegion');
OG_HIDE_LAYER(the_layer, win);
END;
Back in the Object Properties window, select Activate_lCompany as the procedure and make sure the event type is Mouse Button Down. Now set up the parameter by clicking on the Drill-down tab, choose region_name for the Set Parameter field, and use NAME for the To Value of field. Set the Execute Query to Sales by Company. Click OK to save your new settings and then run the application. Click on the chart for North America and you should see a chart like the one in Figure D.
Unfortunately, you have to exit and rerun the application to select a different region. Let's change the lCompany layer so that with a mouse click we'll return to the Sales by Region chart.
Under the Object Navigator, expand the node lCompany and double-click the node Button Procedure. This will bring up the PL/SQL editor. Replace the existing template with the code in Figure E.
PROCEDURE Activate_lRegion (
buttonobj IN og_object,
hitobj IN og_object,
win IN og_window,
eventinfo IN og_event) IS
the_layer OG_LAYER;
BEGIN
the_layer := OG_GET_LAYER('lRegion');
OG_ACTIVATE_LAYER(the_layer, win);
the_layer := OG_GET_LAYER('lCompany');
OG_HIDE_LAYER(the_layer, win);
END;
Click Compile and make sure there are no errors. Now run the application again. When you select a region, the corresponding detail chart comes up. Click on the chart and you return to Sales by Region; you can make another selection if you want.
If we go back and review the requirements, the only one we left out was that the data had to be current within 24 hours. Because our tool reads the data straight from database, we don't have to worry about importing data to the vice president's PC every night. The data is as current as possible whenever he runs his reporting package.
[Return to Index for Exploring Oracle Developer/2000 and Designer/2000 - January 1996]
Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved.
Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis
Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of
Ziff-Davis Publishing Company.
(Sun Microsystems, Inc.)(1997).iso/products/Cobb/ord9601/eod9616a.gif){kind=link}
(Sun Microsystems, Inc.)(1997).iso/products/Cobb/ord9601/eod9616b.gif){kind=link}
(Sun Microsystems, Inc.)(1997).iso/products/Cobb/ord9601/eod9616d.gif){kind=link}